All Categories :
Servers
Chapter 14
Publishing Spreadsheets on the Internet
CONTENTS
In Chapter 13, "Word Processing on
the Web," you learned how to set up your word processor as
a Web browser helper application. In this chapter, you add to
that knowledge and learn how to set up your Intranet so that customers
can retrieve spreadsheet data files using their Web browser and
automatically open the files in their favorite spreadsheet software
for revision, what-if analysis, recalculation, graphing, and more.
I focus on examples that use Excel and Explorer, Mosaic, and Netscape.
If your organization uses other spreadsheet packages, you'll still
be able to easily apply the ideas, if not the step-by-step instructions,
in this chapter. Indeed, you can apply the ideas from this chapter
and Chapter 12 to almost any computer
program in the office.
Later in the chapter, I touch on the issue of portability of spreadsheet
data files from one package to another. The chapter ends with
a discussion of how spreadsheets can be put to great advantage
on the Intranet and demonstrates some sample applications.
One of the reasons for the great success of Microsoft Windows
is how well it integrates application software like spreadsheets
into the desktop environment, enabling the user to start up any
spreadsheet with a mouse click.
Integrating your spreadsheet in your Intranet is, in a sense,
creating a new graphical interface for users based on your Web
browser. Doing so allows you to tie in links to your everyday
tools, like your spreadsheet, and provide a new interface with
a common look and feel that's both easy and fun to use.
More importantly, you can use your Web server, HTML, your spreadsheet
package, and other tools to put together shareable data warehouses
of spreadsheet data files. Such libraries can be complete with
searchable indices and point-and-click access through your Web
browser. Locating and displaying a spreadsheet data file is no
longer a process of finding a file, and then starting up your
spreadsheet to read or use it. Using a Web browser, your customer
can instead click a Web hyperlink and have the spreadsheet fire
up with a copy of the data file loaded, all in one simple process.
After the spreadsheet program has loaded the file, your customers
have all the capabilities of the spreadsheet program at their
disposal; they can change the data, recalculate it, graph it,
print it, save it, and so on.
As with commercial groupware packages like Lotus Notes (see Chapter 28,
"Collaboration on Your Intranet"), expensive data warehouse
software packages are available, but you can easily replicate
many of their features in your Intranet. Moreover, you can do
so at a substantially lower cost and without requiring users to
learn to use yet another new software package. Because they'll
be using their familiar Web browser as the interface to your homegrown
data warehouse and their everyday spreadsheet package to examine
and manipulate the stored data, you need not purchase another
package. In fact, and I realize I'm repeating myself here, one
of the greatest things about the Intranet is that the Web browser
is so inexpensive to deploy (Mosaic and Explorer are free).
You can store company sales or production data, for example, on
your Web server in spreadsheet format, complete with formulas
and macro commands. Properly set up, your Web server can provide
those data files when users select hyperlinks pointing to them.
Web browsers can then take the data and hand it off to your customers'
spreadsheet package, which they can use to play with the numbers
for forecasting or preparing presentation graphics.
Even if you use your Intranet exclusively to serve spreadsheet
data files, you may well spend less money on hardware and software
to do so than you would spend in buying a commercial data warehouse
package. If your Web is already serving other purposes, a few
minutes' work can set it up to function as an essentially free
data warehouse. Although these commercial packages have features
that you won't be able to replicate in your Intranet, the price
of implementing those features you can replicate is certainly
right, and you may find that you're able to replicate enough of
these features to make it unnecessary to buy a data warehouse
package.
You'll recall from earlier chapters the terms helper application
and external viewers to refer to computer programs that
your Web browser uses when it can't directly display a type of
data that you have downloaded. In this chapter, your spreadsheet
package is treated as a helper application. However, to avoid
pointless repetition here in the fundamental setup and configuration
of helper applications, I'll often refer you to Chapter 12,
"MIME and Helper Applications," and Chapter 13,
"Word Processing on the Web." The information in those
chapters is all that you need to configure your Intranet to serve
spreadsheets.
Regardless of which spreadsheet package you use, your first step
in setting up your spreadsheet as a helper application is to configure
your Web server to know about your spreadsheet's data files. With
small differences, as are noted in context, the instructions in
this section apply to any spreadsheet, and even to any word processor
(as discussed in Chapter 13).
As you learned in Chapter 12, Web servers
use the MIME mechanism to identify documents according to their
MIME data type/subtype. Remember that the MIME mechanism divides
data into a relatively small handful of data types, with each
type further subdivided into subtypes. Spreadsheet data files
fall into the application data type.
You also learned in Chapter 12 how Web
servers use filename extensions to map data files on the server
to a MIME data type/subtype. You associate the filename extensions
with MIME types/subtypes in the Windows NT Registry on the Web
server, if you are using IIS, or in a configuration text file,
if you are using some other Windows NT Web servers.
Note |
Please refer to the section titled "Editing the Registry MIME Map" in Chapter 13 for detailed information about configuring IIS. The concepts described in that section for word processors can be applied to spreadsheets as well.
|
Assuming that you've completed the setup of your Web server to
properly identify and serve your spreadsheet data files, your
next step is to populate your server with some data files for
your spreadsheet program. The Explorer in Windows NT 4 makes this
task easy. All you have to do is share the drive or directory
on the Web server, and map the drive or directory on the computer
that you want to copy the files from (again using Explorer or
Network Neighborhood).
To share a folder in NT, just right-click it in Explorer. That
opens a context-sensitive menu. Slide the mouse down to the item
called Properties at the bottom. Click the Sharing tab, and you
will see a dialog very similar to one in Figure 14.1. Just click
the Share As radio button to share the drive. The default share
name is probably fine (in the context of this discussion), and
you don't have to get into the security options (by clicking the
Permissions button in this example) unless you feel the need to
do so.
Figure 14.1: Sharing a folder in the Windows NT Explorer is as easy as 1-2-3.
Windows often enables you to do things more than one way. The
context-sensitive menu from the preceding paragraph also includes
an item that takes you directly to the Sharing tab. Furthermore,
you can go to the Sharing tab from the Explorer menubar by choosing
File | Properties | Sharing (assuming you have already selected
the given file or folder in the lower left pane window).
You also can copy files through shared network drives if your
Web server is in a separate domain from the data files, unless
your system administrators have chosen not to configure a trust
relationship between the domains for security reasons. If you
have no trust relationships between domains, regardless of whether
the Web server is located an inconvenient physical distance away,
you can fall back to the traditional UNIX method of copying files
on the network: FTP. Consult Chapter 9,
"Adding FTP and Gopher Services," for information about
running the IIS FTP service and then you can happily copy files
to your server using an FTP client program, such as CuteFTP included
on the CD-ROM with this book. Whatever method you use, you should
put the files in a dedicated directory on your Web server for
your particular spreadsheet package.
Next, you must create some sort of HTML listing of the data files.
This listing can be as simple or as complex as you like; setting
up hyperlinks to spreadsheet data files is exactly the same as
setting up links to other kinds of documents. A simple HTML listing,
using Microsoft Excel as an example, might be something like the
following (adapted in seconds from the code in Chapter 13):
Listing 14.1. This HTML code provides links to spreadsheet
files.
<HTML>
<HEAD>
<TITLE>Excel Spreadsheets</TITLE>
</HEAD>
<BODY>
<H1>Microsoft Excel Spreadsheets</H1>
This directory contains a set of Excel spreadsheets.
Just click on one to open it up in Excel.
<UL>
<LI><A HREF="qrtrly_sales.xls">Spreadsheet 1</A>,
Last Quarter's Sales</LI>
<LI><A HREF="qrtrly_prod.xls">Spreadsheet 2</A>,
Last Quarter's Production</LI>
<LI><A HREF="cpi_forecast.xls">Spreadsheet 3</A>,
Consumer Price Index Forecasts</LI>
</UL>
If you haven't already done so, you must set up your Web browser
to understand Excel spreadsheet data files.
<A HREF="excel_setup.html">Here are instructions.</A>
</BODY>
</HTML>
Now that you have set up your Web server to know about spreadsheet
data files and populated it with some actual data files from the
spreadsheet program, your next step is to set up Excel as a helper
application. The concept of setting up helper applications for
Web browsers is a general one, but the steps for going about it
differ depending on the Web browser you're using. Please see the
section titled "Setting Up Microsoft Word as a Helper Application
on the Client" in Chapter 13 for
detailed information about how to accomplish this step in both
Mosaic and Netscape. If you're using another browser, you may
need to take a look at its documentation to find out how to accomplish
this step.
Just keep in mind as you go that Excel uses .xls
as the default extension for spreadsheet documents. You should
also add .xlm to the MIME
map to cover Excel macro spreadsheets that your customers have
written or want to retrieve. Further, .xcl
and .xlw are also widely
used.
Setting Up Excel or Lotus as a Helper Application for Your
Macintosh Customers
There is no requirement that every client machine on a Windows
NT network be a PC. Independent of the Intranet, Windows NT 4
Server provides excellent connectivity to Macintosh computers
using the AppleTalk protocol. (Obviously, to take advantage of
the Intranet, a Macintosh will need to have its own TCP/IP software
loaded in addition to, or instead of, AppleTalk.) If you have
Macintosh users in your organization, you may need to provide
them with Intranet access to spreadsheet documents. After all,
once you've gone to the effort of setting up the server, you may
as well get the most benefit out of it by letting everyone participate.
Macintosh users will find the process of setting up Excel or Lotus
1-2-3 as a viewer/helper application virtually identical to the
process for Windows PCs. The only difference is in specifying
the folder and executable names for the spreadsheet program. You
can use the Browse process, just as in Windows, with the browser
displaying Mac folder names in place of PC directory names.
Other Spreadsheet Packages
You should be able to use the general principles described in
this chapter to set up other spreadsheet packages as helper applications.
As each of the preceding sections has shown, the major steps in
setting up a program as a helper application are as follows:
- Add entries to the MIME map on your Web server for your spreadsheet's
data files.
- Set up some data files on your Web server.
- Provide an HTML page to link to the documents.
- Configure your spreadsheet as a helper application for your
Web browser using the fill-in screens shown in Chapter 13.
Because your organization may use multiple spreadsheet packages,
you should anticipate your customers' needs for data file portability.
This section discusses two options:
- Supporting multiple spreadsheet formats
- Converting spreadsheet data to other formats
Supporting Multiple Spreadsheet Data File Formats
Your organization may use more than just a single spreadsheet
program, and, if so, you're probably wondering how you make the
various data files portable. Like many word processors, many spreadsheet
packages can read data files in other spreadsheet programs' native
formats. Excel, for example, can directly read 1-2-3 files, and
1-2-3 can read Excel files. As a result, if your company uses
multiple spreadsheet packages, you can share your data among them
using their capabilities to read each others' data file formats.
Warning |
Be careful not to use duplicate entries in your Web server's MIME map. For example, don't do the following:
application/msexcel xls xcl wks
application/lotus wks xls xcl
application/xess3 xs3 wks
Web servers read the MIME map linearly, from top to bottom, until they find a match of filename extensions. With entries like those in the preceding example, your Web server's MIME type/subtype header information for .wks files will always be read as application/msexcel. This may not be what you want if you have Lotus or Xess customers.
Each spreadsheet customer in your Web will need to configure his browser based on the spreadsheet he uses. Web browsers will override the MIME type/subtype header information they receive if you have configured them to do so.
|
Any new MIME type/subtype information you've added in your helper
application setup in your Web browser is added to the browser's
internal list. If you set up your browser to use 1-2-3 as a helper
application when it encounters the filename extension .wks,
it will use 1-2-3 on any file with that extension, even if one
of the following things happens:
- The server provides no MIME type/subtype
information at all, as is the case with an FTP or Gopher server.
- The server does provide MIME type/subtype
information, but that information is for a different spreadsheet
package.
In other words, your local helper application setup can both fill
in missing MIME type/subtype information and override what it
gets from the Web server. Suppose you've set up your Web server's
MIME map to have entries like the following:
application/msexcel,xls,,5
application/msexcel,xlc,,5
application/lotus,wks,,5
Further suppose that a particular customer has only the 1-2-3
spreadsheet package on her PC. Because you know Excel can directly
read 1-2-3 data files, you can extend the preceding Excel helper
application setup by adding a 1-2-3 filename extension. For example,
Mosaic should be set up to use Excel not only for files with the
.xls, .xlm,
.xlw, and .xcl
extensions, but also for those with .wks.
Note that you can modify the Mosaic Description of MIME Type to
include a reminder of this change.
In addition to some spreadsheets being able to directly read the
data files created by others, most have a Save As feature that
allows you to save a native data file in some other spreadsheet
package's format. Excel, for example, supports saving data files
in several variations of Lotus 1-2-3's .wks
format, as well as several others. Lotus has a similar feature,
enabling you to save data files in Excel's .xls
format.
There's also a semi-universal spreadsheet data file format many
packages support, the Symbolic Link Format, that often uses a
filename extension of .slk.
(This format is much like the Rich Text Format used in making
word processing documents portable.) You can use this format to
make multiple copies of spreadsheet data files on your Web server
with versions for each spreadsheet package your customers use.
Alternatively, because most packages support the Symbolic Link
Format, you may just want to use that format for all your data
files. If you do so, be sure your Web server's MIME map and your
customers' browser setup correspond.
Converting Spreadsheet Data to Other Formats
Both Excel and 1-2-3 have Save As functions that enable you to
not only save spreadsheet data files in other spreadsheet formats,
but also to save a spreadsheet in plain text format. Xess (for
UNIX) has the same capability, along with the capability to save
in PostScript format. Although recalculation and other capabilities
are lost, of course, when a spreadsheet is saved in plain text
or PostScript, the tabular layout and data are preserved. You
can view these plain text files in your Web browser just like
any other text file. You can view PostScript files with an appropriate
PostScript viewer helper application.
Jordan Evans of the U.S. National Aeronautics and Space Administration
has written XL2HTML, a Visual Basic for Applications (VBA) macro
for Excel 5.0 that converts an Excel spreadsheet into an HTML
table. Written for Excel 5.0 for Windows, the macro also works
on both Macintosh Excel 5.0 and the Windows 95 version, Excel
7.0 (according to its author). A copy is on the CD-ROM that accompanies
this book. You can specify a range of cells to be converted, and
XL2HTML outputs HTML Table markup and retains character formatting,
such as boldface, and underlining, from the original spreadsheet.
You can learn more about XL2HTML at http://www710.gsfc.nasa.gov/704/dgd/xl2html.html;
instructions are included. As with spreadsheet files saved in
plain text, customer interactivity (the ability to change and
recalculate the spreadsheet) is lost in XL2HTML, and it would
not be when using the spreadsheet program itself as a helper application.
Nonetheless, you may find situations in which XL2HTML is useful
for your Web.
Microsoft now has available for free download from its Web site
(http://www.microsoft.com)
a version of its Internet Assistant (IA) for Excel (also available
on the CD-ROM that accompanies this book). Like the companion
IA products for Microsoft Word and PowerPoint, this package enables
you to save Office data files (in this case, Excel spreadsheets)
directly to HTML format for use on your Intranet. IA supports
version 5.0 of Excel in both Windows 3.1 and NT and Macintosh
and version 7.0 on Windows 95.
Tip |
While you're visiting the Microsoft Web site, you may also want to download a copy of a read-only Excel Viewer for Windows 95. Just as you set up Excel itself as a Web browser helper application, you can set up the Excel Viewer as one for customers who don't have their own copy of Excel. Although a version of the Excel Viewer for Windows is included on the CD-ROM with this book, you may need to obtain a more current version directly from the Microsoft Web site in the future.
|
The rest of this chapter focuses on what you might do with spreadsheets
on your Intranet. The ideas presented are meant to serve as examples;
I encourage you to creatively adapt them to the specific needs
of your own organization.
If you're into spreadsheets, perhaps you like to compare costs.
Expensive data warehouse software packages are available from
a number of vendors. These packages accumulate information from
a variety of sources around an organization and provide database-like
front ends, allowing searches for mostly numerical data. Data
sources can include a number of different corporate databases
running on different platforms. Once located, the data can be
downloaded for local number crunching to users' PCs or workstations,
usually in a spreadsheet package.
In this section, you'll learn about using your Intranet as a data
warehouse, making full-blown, ready-to-run spreadsheet data files
available to your customers. Although less capable than true data
warehouse packages, your Intranet data warehouse shares many features
with its larger cousins and at a much smaller price. You may find
the trade-off worthwhile.
Cliché has it the spreadsheet was the first killer app
for the PC, so it's no surprise that spreadsheet applications
are used in many organizations. Individual users create and maintain
their own spreadsheets, sometimes for personal use, sometimes
shared. Often, the numbers used in the spreadsheets are company-wide
data on sales, production, and other statistically measurable
information. Collectively, your customers probably already have
a great deal of useful data in spreadsheet format that could be
quite valuable on your Intranet.
Of course, you probably don't want to go snooping on your Intranet
customers' PCs or in their private fileserver directories for
candidate spreadsheet files. But you should encourage your customers
to contribute information for your Intranet. As you'll see, the
collaborative aspects of making spreadsheets available on your
Intranet should encourage customers to contribute. Although locating
documents in this way may be a tedious, logistical problem, it's
still possible, and what you find will provide the basis of your
Intranet spreadsheet data.
The main principles of this activity are those already outlined,
in this chapter and in others, with respect to the conversion
of your legacy data for your Intranet. Where you have spreadsheet
data available, you'll want to use what you learned previously
to move this data quickly onto your Intranet, making it accessible
via your Web server. Your customers will use their Web browsers
to retrieve the data files into the spreadsheet helper applications
(such as Excel, or the Excel Viewer) running on their own machines.
If you have multiple spreadsheet applications in use in your company,
you may need to select a common spreadsheet data file format,
such as standard Lotus format, which most other spreadsheets can
read, or the exportable SYLK format. (See your spreadsheet documentation
for its capabilities in this area.) Even though many, or even
most, of your customers have the necessary spreadsheet software
available to them, you might also want to make plain-text versions
of the data files available for the benefit of customers who don't
have the software. For example, a company schedule built in Excel
could be saved in text format without losing the tabular formatting.
Either Internet Assistant for Excel or the XL2HTML VBA script
mentioned earlier in the chapter can help out with this task.
As you've learned, the basic setup of Web pages containing simple,
clickable lists of available documents is quite easy. Adding a
little subject matter organization is simple, too; use hyperlinks
to create nested menu listings and add explanatory text to the
pages. In just a few minutes, you can present a useful list of
available spreadsheet data files to your customers.
For example, monthly reports can contain clickable cross-references
to other documents, statistical tables, live spreadsheet data
files, images, or even earlier months' reports (all with the same
kinds of embedded links). Your customers can then use their Web
browsers to jump from one document to another, looking for answers
to questions by following promising threads. Where they come across
spreadsheet data, they'll be able to look at it and even manipulate
it using their own spreadsheet software as Web browser helper
applications.
The more cross-references and hyperlinks you're able to add, of
course, the more capabilities you'll give to your customers. To
give your customers even more options for searching for specific
information, you can index the contents of your spreadsheet data
files. (This topic is covered later in the chapter.)
Note |
Remember that helper applications, like your customers' spreadsheet application, always operate on a copy of the original spreadsheet data file. Your original remains unchanged on your Web server until you change it. Customers can freely change the data in the spreadsheet they've opened with a Web browser helper application (assuming the helper application is not a viewer only) for their own needs, all without touching the original. This situation is directly analogous to the data warehouse, where copies of corporate data are downloaded for local processing without changing the original source data. The purpose of these Intranet spreadsheets is simply to provide raw corporate data, such as sales numbers, or production numbers.
|
How will your customers locate spreadsheet data they want without
having to go through long on-screen lists of filenames? Subject-oriented
menus can help, but only up to a point. Beyond a certain number
of files, such a system would become time-consuming. Chapter 21,
"Indexing Your Intranet with WAIS," discusses a powerful
tool for indexing data on your Intranet. This tool enables your
customers to search and retrieve Intranet data from indexes of
the files on the server.
If you're familiar with indexing tools on the Internet, you know
that they are usually text-based. You probably wonder how binary
files such as spreadsheet data files can be indexed with a text-based
tool. Experimentation when reading Excel data files reveals that
useful indexes can be created. Character strings in spreadsheet
files (and other binary files, such as word processor documents)
can be rooted out. Virtually all spreadsheet files contain some
character strings, primarily as column and row labels; they would
not be worth much without these labels to identify the data that
the columns contain.
Spreadsheets may also contain significant amounts of text in individual
cells as well as in column or row labels. Your Intranet spreadsheet
files, when carefully indexed with the right indexing tools, are
nearly as searchable as your plain-text data. Searching these
indexes is quite fast because, unlike all-text files, the word
count of text in most spreadsheet files is quite small and has
a limited vocabulary.
Results of your searches show as lists of clickable Web browser
hyperlinks, showing the hits on your search keywords. Just as
selecting such a hyperlink pointing to a text or HTML document
displays the document for viewing in your browser, so does selecting
a spreadsheet data file hyperlink. The difference is the spreadsheet
data file is handed off to your customer's spreadsheet helper
application for viewing. And, of course, once loaded, the spreadsheet
is an interactive entity your customer can use, not a static Web
page that just sits there.
Tip |
Mosaic gives downloaded spreadsheet data files a temporary filename (such as mos2.wk4). Netscape generates a completely random temporary filename for spreadsheet files you open. To save the file, give it a meaningful name and save it in a permanent place in your system.
|
The EMWAC WAIS package included on the CD-ROM with this book is
an indexing tool that is distributed as freeware. Depending on
the extent and nature of the overall library of documents on your
Intranet and your customers' indexing needs, you may want also
to look into commercial full-text indexing tools.
Assuming you've made spreadsheet data files available on your
Intranet, this section examines how your customers might use these
files.
Intranet Spreadsheets Versus Data Warehouses
Because of the generic nature of data accessible from standard
data warehouse applications, the numerical data customers retrieve
is not in spreadsheet format, ready for them to use. Rather, it's
in raw, plain text, as columns of numbers or text with the entries
on each line separated by a field separator. A spreadsheet package
can import such raw data, including both numerical data and text
row and column headings, provided the customer tells it about
the format of the incoming data.
Microsoft Excel, for example, uses its TextWizard feature to prompt
the customer through the importing of the incoming data. Lotus
1-2-3 accepts only a few standard field separators and also requires
row and column labels to meet a specific format. Thus, even though
customers can bring rows and columns of text and data into their
spreadsheet package, the resulting spreadsheet isn't of much more
value than a plain tabular listing of numbers and text. Customers
have to take the time to add formulas and other spreadsheet-specific
features. For the imported data to be immediately useful, each
customer needs to have spent time building spreadsheet templates
containing the necessary housekeeping that allows imported raw
data to be dealt with.
Negative-sounding comparisons between commercial data warehouse
applications and spreadsheet applications on your Intranet shouldn't
be taken too far. Data warehouse packages have many strong features
not present in the more limited situation described in this chapter.
For instance, data warehouse packages can browse a variety of
sources, including multiple corporate databases, and then integrate
the chosen data into a single spreadsheet for ad hoc manipulation.
Canned Intranet spreadsheets can't match this capability, though
it can be roughly replicated through saving and combining individual
downloaded spreadsheets.
Your trade-off is between cost and features. (Cost includes more
than purchase price; it also includes staff time.) If you need
the advanced features an industrial-strength data warehouse package
provides, you'll want to get one. Nevertheless, you don't want
to ignore the ability to replicate and improve on some of these
packages' features on your Intranet at very low cost. If you can
get 75 percent of the features of a data warehouse package for
five percent of its cost by integrating replication into your
Intranet, you may still be ahead of the game, even taking into
account staff costs. Only you can decide on the value of the remaining
25 percent.
Keep in mind that Web interfaces to the major data warehouse packages
haven't arrived on the scene yet. Because your overall objective
in using Web technology for your Intranet is to enable use of
your customers' Web browsers as front ends to as much organizational
data as possible, this is a critical point. Each vendor's data
warehouse has its own user interface, and although these can be
perfectly good, user-friendly graphical interfaces, each one is
different. Customers have to learn to use the interface before
it's much good to them. On the other hand, your customers already
know how to use their Web browser.
The critical difference between the generic data warehouse approach
and the Intranet spreadsheet approach becomes clear in this context.
The spreadsheet your Intranet customers access by clicking a Web
page hyperlink is already a live, ready-to-run spreadsheet in
their own spreadsheet packages' formats. Formulas, spreadsheet
layout, and all the other housekeeping details are already in
place.
If you provide, for example, spreadsheet data files containing
corporate revenue, expense, and inventory information together
with appropriate formulas linking the information, customers can
do quick what-if analyses by changing some of the numbers in their
temporary copy of the spreadsheet or by resorting the data to
get different views of it. Similarly, interactive macro commands
can be built into commonly accessed spreadsheets. Thus, customers
can run their spreadsheet's statistical-analysis tools (frequency
distribution, regression analysis, data matrices, and so on) on
a range of spreadsheet cells they interactively select.
What Can You Do with an Intranet Spreadsheet?
What can you do with an Intranet spreadsheet? The quick and easy
answer to this question is, of course, anything you can do with
any other spreadsheet. Although this answer is both obvious and
true, it's not a sufficient answer to the question. To answer
this question in the context of your Intranet, think about how
your customers might use the spreadsheets you make available to
them.
The World Wide Web is revolutionary, enabling technology. It provides
new, easy-to-use ways for people to view and use information using
their computers. Your customers use their Web browsers, not their
standard operating system or windowing interface or data warehouse
graphics front end, to access your Intranet spreadsheet data.
Your spreadsheet data files are among all the other files available
on your Intranet, all of which are accessible by a simple point
and click.
By making corporate spreadsheet files available through an internal
Web, you will be giving powerful new tools to your customers who
use that data. Moreover, the audience of potential users of the
data is also vastly increased. Customers who might never use the
corporate data filed away in filing cabinets or in annual stockholders'
reports, now might begin to use it, potentially benefiting themselves
and your company. Putting that data in the form of Web page hyperlinks
makes it accessible to everyone in your Intranet. Although doing
so might lead on occasion to unqualified people acting as junior
actuaries, there's much to be said for making unclassified statistical
information widely available to the members of the organization.
Collaborative Aspects of Spreadsheet Sharing Through Your
Intranet
As discussed in Chapter 28, spreadsheet
data is yet another way in which you can enable your customers
to collaborate using your Intranet. Unlike static Web pages, Intranet
spreadsheets can be used by customers to do things. Even more
important, a spreadsheet is an original creation, aimed at making
it possible to view statistical data in some unique way, with
the additional ability of refining or changing that view through
what-if and other analysis. A spreadsheet is much like the results
of an experiment published on your Intranet with the program the
scientist wrote to perform the experiment. The data, methodology,
and means of rerunning the experiment (that is, the spreadsheet
itself) is made available to others who can attempt to replicate
the results, possibly refine the view of them, and, of course,
use them.
Customer-created spreadsheets, with results based on their unique
views of common data, can be made readily accessible by their
creators to the rest of your Intranet. Other customers who use
these grass-roots spreadsheets are likely to offer comments and
improvements to them, stimulating and feeding a collaborative
process, generating new ideas, solving research or production
problems, and increasing your organization's overall body of knowledge.
A Vital Business Example
Here's a story that may help you see the potential of spreadsheet/Intranet
integration. Frank, a salesman for Amalgamated Enterprises can't
understand why his clients have started complaining about slow
delivery of the widgets he's sold them. Using his laptop, Frank
plays with corporate figures in a Lotus 1-2-3 spreadsheet, discovering
there's plenty of widget inventory. He probes further and hits
on what he thinks might be the problem, although he isn't sure.
Deciding to use the Amalgamated Enterprises Intranet to share
his analysis, Frank creates a simple Web page (he's not an HTML
expert, so it's pretty plain) with a link to his spreadsheet.
He uploads both files to the Web server in the home office using
his laptop from his motel room in Boise. The Web page points out
a potentially dangerous trend in the delivery of widgets, which
portends production and inventory problems. Frank has put his
analysis into spreadsheet form, made the spreadsheet accessible
on the Amalgamated Enterprises Intranet, and asked for comments.
If necessary, Frank can then send e-mail to key people asking
them to check out his Web page at their convenience. With this
simple stroke, Frank has brought everyone's attention to a problem
that may threaten the business. All who want to look at Frank's
data need only click his link to do so. If they want to contact
Frank, who's on the road, all they need do is click a mailto
hyperlink to send him e-mail. (Now, I'd like to keep this story
as realistic as possible, so given that Frank is a salesman, let's
assume that an engineer taught him how to include the mailto
link in his HTML code.)
It's true that this same sort of collaboration could have been
accomplished through any of several other means. Frank could have
sent a group e-mail message with the spreadsheet(s) attached or
entered it in a Lotus Notes forum. He might well have gotten a
good deal of response to either, and possibly even solved his
problem. Nevertheless, Frank's simple, direct Intranet presentation
is quite effective. Anybody on the Amalgamated Enterprises Intranet
can independently view the spreadsheet data using their own spreadsheet
software and refine or change the view of the data, recalculate/reanalyze
it, and otherwise seek to verify or disprove Frank's conclusions.
Other customers may generate new spreadsheet views of the original
data, supporting or disproving Frank's conclusions, and make their
own revised versions available on the Intranet as well, for still
further collaboration and discussion by the group. Frank will
be able to see the new spreadsheet versions and read his e-mail
by the time he gets to Seattle the next afternoon. By the following
day, the shipping bottleneck having been traced to Denver, the
Amalgamated personnel locate 50 flatcars of widgets sitting forgotten
on a railroad siding, and Frank's been given a fat bonus. Hooray!
Nuts and Bolts
Continuing the discussion of the widget crisis, you can see some
of the possibilities for spreadsheet data on your Intranet. As
already discussed, such a situation has a collaborative nature
because many Intranet customers can view and verify data in a
spreadsheet available through a hyperlinked Web page. Some detailed
possibilities for this spreadsheet data include the following:
- Numerical spreadsheet data can be quickly turned into charts
and graphs to further assist in visualizing the data that the
spreadsheet contains.
- The data, including the graphs and charts, can be saved or
printed locally.
- The spreadsheet's data can be combined with other data in
new spreadsheets as a means of verifying or extending the original
data.
- The original or modified spreadsheet data and the graphics
can be exported to other applications. Excel spreadsheets and
graphics can be popped right into Word documents, for example.
- Related corporate databases can be queried for supporting
information, and new data can also be imported either into new,
more elaborate spreadsheets or other documents.
- The generated graphics can be saved as slides for a later
presentation.
- All the resulting documents, data files, slides, and supporting
information can be fed right back onto your Intranet for customer
viewing and further collaboration if desired.
Again, each of these separate activities, even for a single overall
situation such as Amalgamated's widget crisis, could have been
done completely outside the context of your Intranet. Certainly,
the importation of spreadsheet data and graphics into word processing
documents isn't anything new, nor is the creation of slides from
the data using something like PowerPoint, nor is the querying
of databases using the spreadsheet software. This integration
is one of the primary strengths of integrated office packages
such as Microsoft's or Corel's. What is new about this scenario
is that it arose and was played out completely on Frank's Intranet,
with one customer initiating the collaboration and others participating
in the process. And the entire process used Web browsers and browser
helper applications. If Frank was right in identifying shipping
bottlenecks as the culprit, he helped his own sales by keeping
his own clients happy, and he helped Amalgamated Enterprises by
moving inventory to make room for urgent plant expansion-all using
an Intranet.
Admittedly, this example is pretty contrived; not everyone can
save their company from disaster wearing only a Web browser as
a loin cloth. Nonetheless, it shows important possibilities for
collaboration on your Intranet using lowly spreadsheet data. In
a more practical sense, certainly data from multiple hyperlinked
spreadsheets on your Intranet can be combined to form altogether
new spreadsheets. This process is analogous to, though probably
a clumsier process than, the data-warehouse browsing process described
earlier, where data from various sources is combined into new
spreadsheets for analysis. Spreadsheet numbers also can be turned
into charts, graphs, or presentation slides or incorporated into
documents, each of which can be plugged right back into your Intranet
for more customer viewing and collaboration using Web browsers
and helper applications.
Real-Time Intranet Data Feed with a Spreadsheet
Stockbrokers and plant engineers need access to continuously changing
information. Spreadsheet packages can be connected to live stock
market tickers and other live data feeds. Perhaps more widely
useful than a stock market ticker, real-time spreadsheets can
monitor data generated by computer controlled instruments in a
manufacturing facility, refinery, or laboratory. In such a process
control setting, the real-time spreadsheet monitors the instrument's
data file as it grows, continuously loading incoming new data
into the spreadsheet as it arrives. Whenever your Intranet customers
click the link to that process-control spreadsheet, the spreadsheet
package fires up as a helper application to display the latest
set of data. Through the use of automatically executing macro
commands, if available, your spreadsheet could automatically graph
the new data as soon as it is loaded.
For example, a clickable HTML image map could be used to represent
an overall process, with process control sensor icons in the image
map being the hot spots. Clicking one of the sensors reads the
latest spreadsheet data file containing data points that are dynamically
passed to the spreadsheet graphing tool, called as a helper application.
You've learned some practical applications of spreadsheet data
for your Intranet in this chapter. Use of spreadsheets as Web
browser helper applications adds to your Intranet an important
feature of interactivity that was heretofore lacking. This interactivity
enables your customers to do their work in a completely new way.
In addition, you've learned the potential value of collaboration
on your Intranet using spreadsheet data as an example. The following
is a review of what this chapter discussed:
- Why customers want to retrieve spreadsheet
data files directly into their spreadsheet application using a
Web browser
- Configuration of your World Wide Web server
to understand your spreadsheet's document files
- Configuration of your World Wide Web browser
software to handle your spreadsheet data files
- How to support multiple spreadsheet data
formats
- How to survey for available existing spreadsheet
data in your organization
- How to put your spreadsheet data together
so that it's accessible to customers using a Web browser, putting
what you've learned about MIME data types/subtypes and Web browser
helper applications to work
- Indexing tools that can create searchable
indexes of your Intranet spreadsheet data files
- How the use of interactive helper applications,
such as spreadsheets, constitutes important enabling technology
for your customers
- How a sample Intranet spreadsheet can
stimulate collaboration among your customers to solve vital business
problems
- Techniques to use your Intranet spreadsheet
data for ongoing, daily work
The next chapter goes beyond the word processor and the spreadsheet
to consider more Intranet office applications. Some of what's
in that chapter builds on the material in this chapter and the
previous two (Chapters 12 and 13).

Contact
reference@developer.com with questions or comments.
Copyright 1998
EarthWeb Inc., All rights reserved.
PLEASE READ THE ACCEPTABLE USAGE STATEMENT.
Copyright 1998 Macmillan Computer Publishing. All rights reserved.